﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace LimpaGaragem.Framework
{
    public class DataAccess
    {
        #region ENUMERATORS

            public enum QueryType
            {
                Procedure = 1,
                Text = 2
            }

        #endregion

        #region METHODS

            /// <summary>
            /// Retorna um objeto DataTable ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <returns>Objeto DataTable</returns>
            public DataTable ReturnDataTable(string connection, QueryType queryType, string commandText, int timeout)
            {
                Connection objConnection = new Connection();
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                SqlCommand objCommand = new SqlCommand();
                DataTable objDataTable = new DataTable();

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    objDataAdapter.SelectCommand = objCommand;
                    objDataAdapter.Fill(objDataTable);
                    return objDataTable;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

            /// <summary>
            /// Retorna um objeto DataTable ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <param name="timeout"></param>
            /// <param name="parametros"></param>
            /// <returns>Objeto DataTable</returns>
            public DataTable ReturnDataTable(string connection, QueryType queryType, string commandText, int timeout, List<SqlParameter> parametros)
            {
                Connection objConnection = new Connection();
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                SqlCommand objCommand = new SqlCommand();
                DataTable objDataTable = new DataTable();

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    foreach (SqlParameter objParametro in parametros)
                    {
                        if (objParametro != null)
                            objCommand.Parameters.Add(objParametro);
                    }

                    objDataAdapter.SelectCommand = objCommand;
                    objDataAdapter.Fill(objDataTable);
                    return objDataTable;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

            /// <summary>
            /// Retorna um objeto DataSet ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <param name="timeout"></param>
            /// <returns>Objeto DataSet</returns>
            public DataSet ReturnDataSet(string connection, QueryType queryType, string commandText, int timeout)
            {
                Connection objConnection = new Connection();
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                SqlCommand objCommand = new SqlCommand();
                DataSet objDataSet = new DataSet();

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    objDataAdapter.SelectCommand = objCommand;
                    objDataAdapter.Fill(objDataSet);
                    return objDataSet;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

            /// <summary>
            /// Retorna um objeto DataSet ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <param name="timeout"></param>
            /// <param name="parametros"></param>
            /// <returns>Objeto DataSet</returns>
            public DataSet ReturnDataSet(string connection, QueryType queryType, string commandText, int timeout, List<SqlParameter> parametros)
            {
                Connection objConnection = new Connection();
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                SqlCommand objCommand = new SqlCommand();
                DataSet objDataSet = new DataSet();

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    foreach (SqlParameter objParametro in parametros)
                    {
                        if (objParametro != null)
                            objCommand.Parameters.Add(objParametro);
                    }

                    objDataAdapter.SelectCommand = objCommand;
                    objDataAdapter.Fill(objDataSet);
                    return objDataSet;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

            /// <summary>
            /// Executa uma query ou procedure ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <param name="timeout"></param>
            public void ExecuteQuery(string connection, QueryType queryType, string commandText, int timeout)
            {
                Connection objConnection = new Connection();
                SqlCommand objCommand= new SqlCommand();

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    objCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

            /// <summary>
            /// Executa uma query ou procedure ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <param name="timeout"></param>
            /// <param name="parametros"></param>
            public void ExecuteQuery(string connection, QueryType queryType, string commandText, int timeout, List<SqlParameter> parametros)
            {
                Connection objConnection = new Connection();
                SqlCommand objCommand = new SqlCommand();

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    foreach (SqlParameter objParametro in parametros)
                    {
                        if (objParametro != null)
                            objCommand.Parameters.Add(objParametro);
                    }

                    objCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

            /// <summary>
            /// Executa uma query ou procedure ,
            /// o paramêtro "tpQuery" pode ser "text" ou "procedure"
            /// seguido do paramêtro "query" com a "linha de comando"
            /// e será retornado o valor do RETURN 
            /// </summary>
            /// <param name="strConexao"></param>
            /// <param name="tpQuery"></param>
            /// <param name="query"></param>
            /// <param name="timeout"></param>
            /// <param name="parametros"></param>
            public int ExecuteQueryAndReturnValue(string connection, QueryType queryType, string commandText, int timeout, List<SqlParameter> parametros)
            {
                Connection objConnection = new Connection();
                SqlCommand objCommand = new SqlCommand(); ;

                try
                {
                    objConnection.Connect(connection);
                    objCommand.Connection = objConnection.connection;
                    objCommand.CommandTimeout = timeout;

                    if (queryType == QueryType.Text)
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.Text;
                    }
                    else
                    {
                        objCommand.CommandText = commandText;
                        objCommand.CommandType = CommandType.StoredProcedure;
                    }

                    foreach (SqlParameter objParametro in parametros)
                    {
                        if (objParametro != null)
                            objCommand.Parameters.Add(objParametro);
                    }

                    //RETURN VALUE ***********************************
                    SqlParameter objSqlParameter = new SqlParameter();
                    objSqlParameter.ParameterName = "@RETURN_VALUE";
                    objSqlParameter.Direction = ParameterDirection.ReturnValue;
                    objSqlParameter.Value = null;
                    objCommand.Parameters.Add(objSqlParameter);

                    objCommand.ExecuteNonQuery();
                    int valorReturn = 0;
                    int.TryParse(objCommand.Parameters["@RETURN_VALUE"].Value.ToString(), out valorReturn);
                    return valorReturn;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    objConnection.Disconnect();
                }
            }

        #endregion
    }
}
